package sysUtil

import model.XdCell
import org.apache.poi.ss.usermodel.BorderStyle
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.usermodel.CreationHelper
import org.apache.poi.ss.usermodel.DataFormat
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.FillPatternType
import org.apache.poi.ss.usermodel.HorizontalAlignment
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.VerticalAlignment
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.streaming.SXSSFCell
import org.apache.poi.xssf.streaming.SXSSFRow
import org.apache.poi.xssf.streaming.SXSSFSheet
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFFont
import xdTool.XdTableCfg
import xdTool.XdTableColCfg
import xdTool.XdWidgetPanel

import javax.servlet.ServletOutputStream
import javax.servlet.http.HttpServletResponse

/**
 * Created by Administrator on 2019-01-18.
 * 主要用于解析Html获取各种属性
 */
class ExcelUtil {


    //每张sheet记录数到100W时，则将数据插入到第二张sheet
    public static final SHEET_SPLIT_COUNT = 1000000

    //数据库提每次提交记录数
    public static final DB_COMMIT_CACHE_SIZE = 3000


    /**
     * 根据layuitable模型配置，生excel需要的模型
     * @param obj:XdTableCfg.instance || XdTableCfg.id
     * @return 二维数组
     */
    public static List<XdCell[]>  getXdTableToExcelHeader(List layUiCols){
        int colSize = layUiCols.get(0).colspan.sum()
        //先预定义一个二维数组，用于保存每个单元格
        XdCell[][] allRows = new XdCell[layUiCols.size()][colSize]
        List result = new ArrayList()
        for(int rowIndex=0;rowIndex<allRows.length;rowIndex++){//遍历行
            //获取rowIndex行，在数据库中的每个格子
            List rowCells = layUiCols.get(rowIndex)
            //获取第rowIndex行
            def header = allRows[rowIndex]
            for(int i=0;i<rowCells.size() ;i++){
                Map data = rowCells.get(i)
                XdCell xdCell = new XdCell()
                //单元格本身所在的数据库坐标
                xdCell.setX(i)
                xdCell.setY(rowIndex)
                xdCell.setStartRow(rowIndex)
                //设置单元格实际列索引
                ExcelUtil.setRealCell(data,xdCell,header,colSize)
                //针对当前单元格如果有合并的则补充被暂用cell
                ExcelUtil.fillCell( allRows,xdCell)
            }
            result.add(header)
        }
        return result
    }


    /**
     *
     * @param data sql查询数据
     * @param filed excel映射sql字段
     * @return 返回不分页的全部数据
     */
    static List  getXdTableAllBody(List<XdCell> fields,List<Map> data){

        List<List<XdCell>> result = new ArrayList()
        for(int rowIndex = 0;rowIndex < data.size() ;rowIndex++ ){
            List row = new ArrayList<XdCell>()
            for(int colIndex = 0; colIndex < fields.size();colIndex++){
                XdCell temp = fields.get(colIndex)
                XdCell xdCell = new XdCell()
                xdCell.setStartCol(colIndex)
                xdCell.setStartRow(rowIndex)
                xdCell.setRowspan(1)
                xdCell.setColspan(1)
                xdCell.setType(temp.getType())
                xdCell.setDataType(temp.getDataType())
                if(temp.type =="radio"){
                    xdCell.setContent("O")
                }else if(temp.type == "numbers"){
                    xdCell.setContent(xdCell.getStartRow()+1)
                }else if(temp.type == "space"){
                    xdCell.setContent("")
                }else{
                    def a = data.get(rowIndex).get(temp.field)
                    xdCell.setContent(a)
                }
                row.add(xdCell)
            }
            result.add(row)
        }
        return  result
    }

    /**
     * 设置合并单元格
     * @param fields
     * @param excelBody
     */
    static getXdTableMergeCell(List<XdCell> fields,List<List<XdCell>>  excelBody){
        List<XdCell> result = new ArrayList<>()
        def fieldMap = new HashMap<String,XdCell>()//记录每个field位置，方便获取
        for(int i=0;i<fields.size();i++){
            fieldMap.put(fields.get(i).field,fields.get(i))
        }
        for(int colIndex =0;colIndex<fields.size();colIndex++){
            def field = fields.get(colIndex)
            if(field.mergeField){
                field.mergeField.each {target,refer ->
                    def tarField = fieldMap.get(target)

                    if(refer){//如果参照列不为空，则需依照参照列，所占行数进行合并
                        XdCell refField = fieldMap.get(refer)
                        def rowSpan = 0
                        //遍历参照行，获取具有相同行的数量：作为参照范围
                        for(int rowIndex = 0; rowIndex < excelBody.size()  && excelBody.size() >=2  ;rowIndex++){
                            if(rowIndex == excelBody.size()-1 && rowSpan>0){
                                XdCell refCell = new XdCell()
                                refCell.setHeadContent(refField.getHeadContent())
                                refCell.setStartRow(rowIndex - rowSpan)
                                refCell.setStartCol(refField.startCol)
                                refCell.setRowspan(rowSpan+1)
                                refCell.setField(refField.field)

                                ExcelUtil.addTarMergeCell( refCell, tarField.startCol, excelBody, result)
                                break
                            }
                            def refData1 = excelBody.get(rowIndex).get(refField.startCol)
                            def refData2 = excelBody.get(rowIndex+1).get(refField.startCol)
                            if(refData1.content == refData2.content){//如果相等说明可合并
                                //println "refData1:${refData1.content} , refData2:${refData2.content}"
                                rowSpan++
                            }else if(rowSpan > 0 ){//如果不等，则判断合并次数是否>1,如果大于零，则保存合并单元
                                XdCell refCell = new XdCell()
                                refCell.setHeadContent(refField.getHeadContent())
                                refCell.setStartRow(rowIndex - rowSpan)
                                refCell.setStartCol(refField.startCol)
                                refCell.setRowspan(rowSpan+1)
                                refCell.setField(refField.field)

                                ExcelUtil.addTarMergeCell( refCell, tarField.startCol, excelBody, result)
                                rowSpan = 0 ;
                            }
                        }
                        //实际相同的行数
                    }else{//如果参照列为空，则直接合并当前列即可
                        def rowCount = 0
                        //遍历参照行，获取具有相同行的数量：作为参照范围
                        for(int rowIndex = 0; rowIndex < excelBody.size();rowIndex++){
                            if(rowIndex == excelBody.size()-1  ){
                                if(rowCount > 0){
                                    XdCell mergeCell = new XdCell()
                                    mergeCell.setStartCol(tarField.startCol)
                                    mergeCell.setStartRow(rowIndex - rowCount)
                                    mergeCell.setRowspan(rowCount+1)
                                    result.add(mergeCell)
                                }
                            }else {
                                def refData1 = excelBody.get(rowIndex).get(tarField.startCol)
                                def refData2 = excelBody.get(rowIndex+1).get(tarField.startCol)
                                if(refData1.content == refData2.content){//如果相等说明可合并
                                    rowCount++
                                }else if(rowCount > 0 ){//如果不等，则判断合并次数是否>1,如果大于零，则保存合并单元
                                    XdCell mergeCell = new XdCell()
                                    mergeCell.setStartCol(tarField.startCol)
                                    mergeCell.setStartRow(rowIndex - rowCount)
                                    mergeCell.setRowspan(rowCount+1)
                                    result.add(mergeCell)
                                    rowCount = 0 ;
                                }
                            }
                        }
                    }
                }
            }
        }
        return result
    }

    /**
     * 根据参照列，合并目标列单元格
     * @param refCell
     * @param tarCell
     * @param excelBody
     * @param result
     */
    static void addTarMergeCell(XdCell refCell,int startCol,List<List<XdCell>> excelBody,List<XdCell> result){

        int rowSpan = 0; //计数器：目标列合并的行
        int rowIndex = 0
        for(int i = 0; i< refCell.rowspan ;i++){
            rowIndex = refCell.startRow + i
            if( rowIndex == excelBody.size()-1 || i == refCell.rowspan-1 ){
                if(rowSpan > 0){
                    XdCell tarCell = new XdCell(startCol:startCol)
                    tarCell.setStartRow(rowIndex - rowSpan)
                    tarCell.setRowspan(rowSpan+1)
                    result.add(tarCell)
                }
            }else {
                def refData1 = excelBody.get(rowIndex).get(startCol)
                def refData2 = excelBody.get(rowIndex+1).get(startCol)
                if(refData1.content == refData2.content){//如果相等说明可合并
                    rowSpan++
                }else if(rowSpan > 0 ){//如果不等，则判断合并次数是否>1,如果大于零，则保存合并单元
                    XdCell tarCell = new XdCell(startCol:startCol)
                    tarCell.setStartRow(rowIndex - rowSpan)
                    tarCell.setRowspan(rowSpan+1)
                    result.add(tarCell)
                    rowSpan = 0 ;
                }
            }
        }
    }



    /**
     *
     * @param allRows: XdTableToExcelModel配置model,二维数组
     * @return list ：获取字段映射
     */
    static List<XdCell> getXdTableToExcelField(List<XdCell[]> header){
        XdCell[]  fields = new XdCell[header.get(0).length]
        for(int i=0;i<header.get(0).length;i++){//遍历每一列，取出field不为空的节点
            for(int j= 0 ;j < header.size();j++){
                XdCell temp = header.get(j)[i]
                if(temp?.field){
                    fields[i] = temp
                    break
                }else if(temp?.title){
                    fields[i] = temp
                }
            }
        }
        return fields.toList()
    }

    /**
     * 设置实际单元格所在的行列
     * @param data
     * @param xdCell
     * @param colSize //总计列数
     * @param allCells:数组,记录excel每行-单元格位置
     */
    public static void setRealCell(Map data,XdCell xdCell,XdCell[] header,colSize){
        //由于在fillCell方法中，已经将合并的单元格补齐（拆分-主要用于索引单元格实际位置），只需获取当前xdCell所在行
        //找出当前行第一个空单元格-即还未索引到的cell,为当前的xdCell位置
        for(int colIndex = 0 ;colIndex < colSize;colIndex++){
            XdCell temp = header[colIndex]
            if(!temp){//如果遇到为空的，则说明已经找到该xdCell的位置
                xdCell.setStartCol(colIndex)
                break
            }
        }
        xdCell.setField(data.field)
        xdCell.setTitle(data.title)
        xdCell.setRowspan(data.rowspan)
        xdCell.setColspan(data.colspan)
        xdCell.setMergeField(data?.mergeField)
        xdCell.setType(data?.type)
        xdCell.setDataType(data?.dataType)
//        xdCell.setPrimaryKey(data?.primaryKey)
//        xdCell.setPrimaryValue(data?.primaryValue)
        xdCell.setId(data?.id)
        header[xdCell.startCol]= xdCell
    }

    /**
     * 针对sourceCell 当前cell，补齐合并单元格的cell
     * @param allCells
     * @param sourceCell
     */
    public static void fillCell(allRows,XdCell sourceCell){
        int rowspan = sourceCell.rowspan
        int colspan = sourceCell.colspan
        //扩展被合并的单元格
        for(int i = 1 ;colspan == 1 && rowspan > 1  && i < rowspan; i++){//只有合并行
            XdCell xdCell =  new XdCell()
            xdCell.setStartCol(sourceCell.startCol)
            xdCell.setStartRow(sourceCell.startRow+i)
            //设置其x、y，标识来源cell的数据库索引
            xdCell.setX(sourceCell.x)
            xdCell.setY(sourceCell.y)
            allRows[xdCell.startRow][xdCell.startCol]= xdCell
        }

        for(int i = 1 ;rowspan == 1 && colspan > 1 && i < colspan   ; i++){//只有合列
            XdCell xdCell =  new XdCell()
            xdCell.setStartCol(sourceCell.startCol+i)
            xdCell.setStartRow(sourceCell.startRow)
            //设置其x、y，标识来源cell的数据库索引
            xdCell.setX(sourceCell.x)
            xdCell.setY(sourceCell.y)
            allRows[xdCell.startRow][xdCell.startCol]= xdCell
        }

        for(int i = 0 ;rowspan > 1 && colspan > 1 && i < rowspan ; i++){//行列都有s
            for(int j= 0; j < colspan ;j++){
                if(i!=0 || j!=0){
                    XdCell xdCell =  new XdCell()
                    xdCell.setStartCol(sourceCell.startCol+j)
                    xdCell.setStartRow(sourceCell.startRow+i)
                    //设置其x、y，标识来源cell的数据库索引
                    xdCell.setX(sourceCell.x)
                    xdCell.setY(sourceCell.y)
                    allRows[xdCell.startRow][xdCell.startCol]= xdCell
                }
            }
        }
    }

    /**
     *   /**
     * 根据sheet索引，从startRow还是写入数据
     * @param sheetIndex :0 开始
     * @param startRow：行索引，从0开始
     * @param workbook
     * @param headerStyle
     * @param headerData
     * @param bodyStyle
     * @param sheetIndex：0
     * @param startRow：0
     * @param headerRows：1
     * @param excelFields
     * @param data
     */
    static void fillSheetData(SXSSFWorkbook workbook,
                              XSSFCellStyle headerStyle,
                              List<XdCell[]> headerData,
                              XSSFCellStyle bodyStyle,
                              CreationHelper creationHelper,
                              Map indexMap,//,
                              List<XdCell> excelFields,
                              List<Map> data){

        //println SysCommUtil.copyMapList(excelFields)

        Integer sheetIndex = indexMap.get("sheetIndex")
        Integer startRow  = indexMap.get("startRow")
        Integer headerRow = indexMap.get("headerRow")

        SXSSFSheet sheet =  workbook.getSheetAt(sheetIndex)
        for(int i=0;i<data.size();i++){
            //如果
            if((startRow - headerRow) >= ExcelUtil.SHEET_SPLIT_COUNT){
                sheetIndex = sheetIndex + 1
                indexMap.put("sheetIndex",sheetIndex)
                sheet = workbook.createSheet()
                startRow = headerRow
                indexMap.put("startRow",startRow)
                ExcelUtil.fillWorkBookSheetHeader(headerData,headerStyle,sheet)
            }

            SXSSFRow row = sheet.createRow(startRow)
            for(int j =0;j< excelFields.size();j++){
                SXSSFCell cell = row.createCell(j)
                XdCell xdCell = excelFields.get(j)
                //设置cell格式
                if(xdCell.type =="radio"){
                    cell.setCellValue("○")
                    cell.setCellStyle(bodyStyle)
                }else if(xdCell.type == "numbers"){
                    cell.setCellValue(startRow - headerRow + 1)
                    cell.setCellStyle(bodyStyle)
                }else if(xdCell.type == "space"){
                    cell.setCellValue("")
                    cell.setCellStyle(bodyStyle)
                }else{
                    ExcelUtil.setCellFormatValue(cell,bodyStyle,creationHelper,xdCell.dataType,data.get(i)?.get(xdCell?.field))
                }

            }
            startRow++;
            indexMap.put("startRow",startRow)
        }
    }


    /**
     * 获取数据格式
     * @param type
     * @param value
     * @return
     */
    static void  setCellFormatValue(SXSSFCell cell,XSSFCellStyle bodyStyle,CreationHelper creationHelper,String dataType,Object value){

        DataFormat  cellFormat = creationHelper.createDataFormat()
        if(!value && String.valueOf(value).length()==0 ){
            cell.setCellValue(null)
            cell.setCellStyle(bodyStyle)
        }else if(dataType=="Date"){
            def newStyle =(XSSFCellStyle) bodyStyle.clone()
            newStyle.setDataFormat( cellFormat.getFormat("yyyy-MM-dd hh:mm:ss"))
            Date date
            try {
                date = SysCommUtil.getDateByFormatAndStr(SysCommUtil.DateFormat_YYYYMMDD_hh24miss,value)
                cell.setCellValue(date)
            }catch (Exception e){
                cell.setCellValue(value)
            }finally{
                cell.setCellStyle(newStyle)
            }
        }else if(dataType=="Number"){
            //println new BigDecimal(value)
            //def newStyle =(XSSFCellStyle) bodyStyle.clone()
            //newStyle.setDataFormat(cellFormat.getFormat("##0.00"))
            try {
                cell.setCellValue(new BigDecimal(value))
            }catch (Exception e){
                cell.setCellValue(value)
            }finally{
                //cell.setCellStyle(newStyle)
                cell.setCellStyle(bodyStyle)
            }
        }else{
            cell.setCellValue(value)
            cell.setCellStyle(bodyStyle)
        }
    }

    /**
     * 循环查询数据，并写入Excel
     List layUiHeader,
     String sql,
     groovy.sql.Sql db,
     int offset, 页数
     int pageSize, 每次查询多少条记录
     String page,//分页标识：是否分页查询
     String tempDir, //临时文件目录
     String downLoadName, //下载文件名称
     HttpServletResponse response
     */
    public static String CREATE_TMPFILE_EXCEL2007(List layUiHeader,
                                           String sql,
                                           groovy.sql.Sql db,
                                           int offset,
                                           int pageSize,
                                           String page //分页标识
                                           ){

        //1.创建表
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000)
        workbook.setCompressTempFiles(true);
        XSSFCellStyle headerStyle = ExcelUtil.createHeaderStyle(workbook)
        XSSFCellStyle bodyStyle =  ExcelUtil.createBodyStyle(workbook)
        XSSFCellStyle footStyle = ExcelUtil.createFootStyle(workbook)
        //XSSFDataFormat cellFormat = workbook.createDataFormat()
        CreationHelper cellFormat = workbook.getCreationHelper();

        //生成表头
        List headerData = ExcelUtil.getXdTableToExcelHeader(layUiHeader)
         //字段映射
        List<XdCell> excelFields = ExcelUtil.getXdTableToExcelField(headerData)

        SXSSFSheet sheet = workbook.createSheet()
        ExcelUtil.fillWorkBookSheetHeader(headerData,headerStyle,sheet)

        //初始化读取行列
        int sheetIndex = 0
        if(page == "true"){
            Map indexMap = [sheetIndex:sheetIndex,headerRow:headerData.size(),startRow:headerData.size()]

            Long totalCount = SysCommUtil.pageCount(sql,db)
            Long pageCount = (totalCount % pageSize == 0 ? totalCount/pageSize : totalCount/pageSize + 1)
            for(;offset <= pageCount ; offset++){
                println "正在写入临时文件xlsx,第${offset}页，${pageSize*offset}条"
                List data = SysCommUtil.pageResult(sql,offset,pageSize,db)
                ExcelUtil.fillSheetData( workbook,headerStyle,headerData, bodyStyle,cellFormat, indexMap, excelFields, data)
            }
        }else{
            List data = SysCommUtil.pageAllResult(sql,db)
            List<List<XdCell>> excelBody =  ExcelUtil.getXdTableAllBody(excelFields,data)
            List<XdCell> excelMerge  = ExcelUtil.getXdTableMergeCell(excelFields,excelBody)
            ExcelUtil.fillWorkBookSheetBodyNoPage(excelBody,bodyStyle,cellFormat,excelMerge,sheet)
            ExcelUtil.fillWorkBookSheetFoot(sheet,footStyle)
        }

        File tempFile = SysCommUtil.createTempFile(".xlsx")
        //println tempFile.getAbsolutePath()
        FileOutputStream excelOutput = new FileOutputStream(tempFile) //写入本地文件
        //def excelOutput = response.getOutputStream() //写入输出流
        workbook.write(excelOutput)
        excelOutput.flush()
        excelOutput.close()
        workbook.dispose()
        return tempFile.getName()
    }


    /**
     * 下载导入模板
     * @param layUiHeader
     */
    public static CREATE_TMPFILE_EXCELTMP(xdTableColCfgList){
        //1.创建表
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000)
        workbook.setCompressTempFiles(true);
        XSSFCellStyle headerStyle = ExcelUtil.createHeaderStyle(workbook)
        //XSSFDataFormat cellFormat = workbook.createDataFormat()
        CreationHelper cellFormat = workbook.getCreationHelper();

        //生成表头
        //List headerData = ExcelUtil.getXdTableToExcelHeader(layUiHeader)

        SXSSFSheet sheet = workbook.createSheet()
        ExcelUtil.fillWorkBookSheetHeader(xdTableColCfgList,headerStyle,sheet)

        File tempFile = SysCommUtil.createTempFile(".xlsx")
        //println tempFile.getAbsolutePath()
        FileOutputStream excelOutput = new FileOutputStream(tempFile) //写入本地文件
        //def excelOutput = response.getOutputStream() //写入输出流
        workbook.write(excelOutput)
        excelOutput.flush()
        excelOutput.close()
        workbook.dispose()
        return tempFile.getName()
    }

    /**
     * 设置列宽
     * @param sheet
     */
    public static setSheetProperty(SXSSFSheet sheet){

        int colCount = sheet.getRow(0).getPhysicalNumberOfCells()
        //设置列宽
        for(int i=0;i<colCount;i++){
            sheet.setColumnWidth(i,(int)sheet.getColumnWidth(i) * 35 / 20)
        }
        //设置sheet只读
        //sheet.protectSheet("")
    }

    /**
     *
     * @param response
     */
    public static void DownLoadExcel2007(HttpServletResponse response,String fileName,Object tempFile){
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", String.valueOf("attachment;filename=${fileName}.xlsx"));
        File file
        if(tempFile instanceof String){
            file = new File(tempFile)
        }else{
            file = tempFile
        }

        //response.setContentLength(file.size())
        FileInputStream fileInputStream = new FileInputStream(tempFile)
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream)
        byte[] b = new byte[2048];
        int len = bufferedInputStream.read(b)
        def out = response.getOutputStream()
        while (len>0){
            out.write(b,0,len)
            len = bufferedInputStream.read(b)
        }
        out.flush()
        out.close()
        bufferedInputStream.close()
        fileInputStream.close();
        file.delete()
    }


    /**
     *  作用于分页表格：清单表，不能合并数据单元格
     * @param data sql 数据
     * @param bodyStyle 单元格样式
     * @param excelFields 数据字段映射
     * @param sheet
     */
    static void fillWorkBookSheetBodyPage(List<Map> excelBody,XSSFCellStyle bodyStyle,List<XdCell> excelFields,SXSSFSheet sheet){
        int startRow = sheet.getPhysicalNumberOfRows();
        for(int i = 0;i <excelBody.size();i++){
            SXSSFRow row = sheet.createRow(startRow+i)
            for(int j =0;j< excelFields.size();j++){
                SXSSFCell cell = row.createCell(j)
                cell.setCellStyle(bodyStyle)

                XdCell xdCell = excelFields.get(j)
                if(xdCell.type =="radio"){
                    cell.setCellValue("○")
                }else if(xdCell.type == "numbers"){
                    cell.setCellValue(i+1)
                }else if(xdCell.type == "space"){
                    cell.setCellValue(null)
                }else{
                    cell.setCellValue(excelBody.get(i).get(String.valueOf(excelFields.get(j).field)))
                }
            }
        }
    }
    /**
     * Excel 填值,作用于不分页的表格
     * @param excelHeader
     * @param headerStyle
     * @param excelBody
     * @param bodyStyle
     * @param excelMerge
     * @param headerRows ：起始行 0开始
     */
    static void  fillWorkBookSheetBodyNoPage( List<List<XdCell>> excelBody,
                                              XSSFCellStyle bodyStyle,
                                              CreationHelper creationHelper,
                                              List<XdCell> excelMerge,
                                              SXSSFSheet sheet ){

        //先添加单元格
        int rowOffset = sheet.getPhysicalNumberOfRows()
        int colCount = excelBody.get(0).size()
        for(int i = 0;i < excelBody.size();i++){
            SXSSFRow row = sheet.createRow(rowOffset+i)
            for(int j =0;j<colCount;j++){
                SXSSFCell cell = row.createCell(j)
                //cell.setCellValue(excelBody.get(i).get(j).getContent())
                //cell.setCellStyle(bodyStyle)
                XdCell xdCell = excelBody.get(i).get(j)

                if(xdCell.type =="radio"){
                    cell.setCellValue("○")
                    cell.setCellStyle(bodyStyle)
                }else if(xdCell.type == "numbers"){
                    cell.setCellValue(i+1)
                    cell.setCellStyle(bodyStyle)
                }else if(xdCell.type == "space"){
                    cell.setCellValue(null)
                    cell.setCellStyle(bodyStyle)
                }else{
                    ExcelUtil.setCellFormatValue(cell,bodyStyle,creationHelper,xdCell.dataType,String.valueOf(xdCell.getContent()))
                }
            }
        }

        //添加合并区域
        for(XdCell xdCell1 : excelMerge){
            xdCell1.setStartRow(xdCell1.startRow+rowOffset)
            sheet.addMergedRegion(new CellRangeAddress(xdCell1.startRow,xdCell1.endRow,xdCell1.startCol,xdCell1.endCol))
        }

    }

    /**
     * 创建表格页脚行
     * @param sheet
     */
    static void fillWorkBookSheetFoot(SXSSFSheet sheet,XSSFCellStyle style){
        int rowCount = sheet.getPhysicalNumberOfRows()
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells()
        SXSSFCell cell  ,cell1

        if(colCount < 4){
            SXSSFRow row = sheet.createRow(rowCount+2)
            cell = row.createCell(0)
            cell.setCellValue("制表人:")
            SXSSFRow row1 = sheet.createRow(rowCount+3)
            cell1 = row1.createCell(0)
            cell1.setCellValue("制表日期:")
        }else{
            SXSSFRow row = sheet.createRow(rowCount+2)
            cell = row.createCell(colCount-4)
            cell.setCellValue("制表人:")
            cell1 = row.createCell(colCount-2)
            cell1.setCellValue("制表日期:")
        }


        cell.setCellStyle(style)
        cell1.setCellStyle(style)
    }

    /**
     * 增加表头部分
     * @param excelHeader
     * @param cellStyle
     * @param sheet
     */
    static void fillWorkBookSheetHeader(List<XdCell[]> excelHeader,XSSFCellStyle cellStyle,SXSSFSheet sheet){
        for(int i=0;i<excelHeader.size();i++){//遍历行
             SXSSFRow row =  sheet.createRow(i)
            for(int j=0;j<excelHeader.get(0).length;j++){//遍历列
                SXSSFCell  cell =  row.createCell(j)
                XdCell temp = excelHeader?.get(i)[j]
                cell.setCellStyle(cellStyle)
                cell.setCellValue(temp.getHeadContent())
                if(temp.colspan > 1 || temp.rowspan >1 ){
                    sheet.addMergedRegion(new CellRangeAddress(temp.startRow,temp.endRow,temp.startCol,temp.endCol))
                }
            }
        }
        ExcelUtil.setSheetProperty(sheet)
    }


    /**
     * 创建Excel-header样式
     * @param wb
     * @return
     */
    static createHeaderStyle(SXSSFWorkbook wb){
        XSSFCellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN)
        xssfCellStyle.setBorderLeft(BorderStyle.THIN)
        xssfCellStyle.setBorderRight(BorderStyle.THIN)
        xssfCellStyle.setBorderTop(BorderStyle.THIN)
        xssfCellStyle.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setRightBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setTopBorderColor(IndexedColors.GREY_40_PERCENT.index)
        //设置背景
        xssfCellStyle.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.index);// 设置背景色
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //自动换行
        xssfCellStyle.setWrapText(true)
        //设置字体
        xssfCellStyle.setFont(createHeaderFont(wb))
        return xssfCellStyle
    }


    /**
     * 创建Excel-body样式
     * @param wb
     * @return
     */
    static XSSFCellStyle createBodyStyle(SXSSFWorkbook wb){
        XSSFCellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN)
        xssfCellStyle.setBorderLeft(BorderStyle.THIN)
        xssfCellStyle.setBorderRight(BorderStyle.THIN)
        xssfCellStyle.setBorderTop(BorderStyle.THIN)
        xssfCellStyle.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setRightBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setTopBorderColor(IndexedColors.GREY_40_PERCENT.index)
        //设置字体
        xssfCellStyle.setFont(createBodyFont(wb))
        return xssfCellStyle
    }

    /**
     * 创建Excel-body样式
     * @param wb
     * @return
     */
    static XSSFCellStyle createFootStyle(SXSSFWorkbook wb){
        XSSFCellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置字体
        xssfCellStyle.setFont(createHeaderFont(wb))
        return xssfCellStyle
    }

    static createHeaderFont(SXSSFWorkbook wb){
        XSSFFont font = wb.createFont()
        font.setBold(true)
        font.setFontHeightInPoints((short)10)
        font.setFontName("宋体");
        return font
    }

    static createBodyFont(SXSSFWorkbook wb){
        XSSFFont font = wb.createFont()
        font.setBold(false)
        font.setFontHeightInPoints((short)10)
        font.setFontName("宋体");
        return font
    }

    /**
     * 读取excle 并写入 panleId 对应的表
     * @param panelId 用于关联导入表
     * @param file 读取的excel文件
     //2.读取文件写入数据到表
     //....2.1读取excel每个sheet，并根据panel-id获取表头行数，以及字段映射
     //....2.2根据传入的panel-id获取表结构
     //....2.2.1判断是主键干预
     //.........a.如果是，则根据配置的主键删除后再插入
     //.........b.如果不是，则无需删除，直接插入
     //....2.3写入批量写入数据，每次读取5000条记录(循环执行2.2步骤)
     //3.删除临时文件
     //4.异常处理
     */

    static ReadAndInsertByTableAndFile(List<XdCell[]> layuiHeader,  deleteSql,insertSql,groovy.sql.Sql db,tempFile){

        //生成表头
        //List headerData = ExcelUtil.getXdTableToExcelHeader(layuiHeader)
        //字段映射
        List<XdCell> fieldMap = layuiHeader?.get(0).toList() //ExcelUtil.getXdTableToExcelField(layuiHeader)

        //readInfo{headRows: 表头所占行数、cacheSize：每次提交的记录数,indexRow：当前写入记录数}
        def readInfo = [headRows:layuiHeader.size(),cacheSize:ExcelUtil.DB_COMMIT_CACHE_SIZE,indexRow:0]
        InputStream inp = new FileInputStream(tempFile)
        Workbook wb = WorkbookFactory.create(inp);

        for(int sheetIndex =0;sheetIndex<wb.getNumberOfSheets();sheetIndex++){

            readInfo.put("indexRow",0)
            Sheet sheet =  wb.getSheetAt(sheetIndex)
            insertSheet2Table(readInfo,fieldMap,sheet,deleteSql,insertSql,db)
        }
    }

    /**
     * 读取sheet，并插入table
     * @param readInfo [headRows:headerData.size(),cacheSize:5000,indexRow:0,sheetIndex:0]
     * @param [XdCell]
     * @param sheet
     * @param sql
     */
    static insertSheet2Table(Map readInfo,List<XdCell> fieldMap,Sheet sheet,deleteSql,insertSql,groovy.sql.Sql db){


        int headRows = readInfo.get("headRows")
        def cacheSize = readInfo.get("cacheSize")
        def indexRow = readInfo.get("indexRow")

        List<Map> cacheParam = []

        if(indexRow == 0 ){
            indexRow = headRows
        }

        for(int i=0 ;indexRow < sheet.getPhysicalNumberOfRows() ; i++ ){
            //遍历行
            Row row = sheet.getRow(indexRow)
            Map param = [:]
            //遍历列
            for (int colIndex = 0 ; colIndex < fieldMap.size();colIndex++ ){
                XdCell xdCell = fieldMap.get(colIndex)
                if(xdCell.field){//如果列中field字段不空（非伪列）
                    Cell cell = row?.getCell(colIndex)
                    def value = ExcelUtil.getCellValue(cell,xdCell)
                    param.put(xdCell.field,value)
                    //设置是否执行删除操作标识
                }
            }

            cacheParam.add(param)

            indexRow ++ ;

            if(i >=  cacheSize || indexRow >=  sheet.getPhysicalNumberOfRows()  ){
            //如果到达提交点或者索引在最后一行
                db.withTransaction {
                    if(deleteSql){//判断是否先删数据
                        for(int ii=0; ii<cacheParam.size();ii++){
                            //println "${indexRow}  ${sheet.getPhysicalNumberOfRows()}  ${deleteSql} :${ii}  ${cacheParam.get(ii)}"
                            def flag=  db.execute(cacheParam.get(ii),deleteSql)
                        }
                    }
                    for(int ii=0; ii<cacheParam.size();ii++){
                        println "${indexRow}  ${sheet.getPhysicalNumberOfRows()}  insertSql : ${ii}"
                        def flag=  db.executeInsert(cacheParam.get(ii),insertSql)
                    }
                }
                cacheParam = []
                i = 0
            }
        }

    }

    /**
     * 根据字段类型获取cell值
     * @param cell
     * @param xdCell
     */
    static getCellValue(Cell cell,XdCell xdCell){
        def data
        try{
            if(cell){
                switch ( cell.cellTypeEnum){
                    case "STRING":
                        data = cell.getStringCellValue();
                        break;
                    case "BOOLEAN":
                        data = cell.getBooleanCellValue()
                        break;
                    case "BLANK":
                        data = null
                        break;
                    case "FORMULA":
                        data = cell.getCellFormula()
                        break;
                    case "NUMERIC":
                        if(DateUtil.isCellDateFormatted(cell)){
                            data = cell.getDateCellValue();
                        }else{
                            data = cell.getNumericCellValue()
                        }
                        break;
                    default:
                        data = null
                        break;
                }

                switch (xdCell.dataType){
                    case "String":
                        data =  data?String.valueOf(data):null
                        break;
                    case "Number":
                        data = data?new BigDecimal(data):null
                        break;
                    case "Date":
                        if(data.getClass().name.indexOf("Date") != -1){
                             //如果单元格本身是日期
                            data = new java.sql.Timestamp(data.getTime());
                        }else{
                            //如果不是日期，则转换日期
                            data = data? new java.sql.Timestamp(new Date(String.valueOf(data)).getTime()):null
                        }
                        break;
                }

            }
        }catch (Exception e){
            //异常设置插入数据为空
            //println "单元格数据转换异常....${cell.getAddress()}"
            data = null
        }finally{
            return  data
        }
    }
}
